using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Collections;
namespace CocaCola_Inventory_System
{
    class retrieve
    {

        OdbcConnection con;

        public retrieve(OdbcConnection connect)
        {
            con = connect;
            

        }

        public void fillDataset(DataSet D)
        {

            OdbcCommand com = new OdbcCommand("select * from items");
            com.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcDataReader reader = com.ExecuteReader();
           
                while (reader.Read())
                {
                    object[] temp = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                        temp[i] = reader[i];

                    D.Tables["items"].Rows.Add(temp);
                }
                reader.Dispose();
                com.CommandText = " select * from details";
                reader = com.ExecuteReader();
                while (reader.Read())
                {
                    object[] temp = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                        temp[i] = reader[i];

                    D.Tables["details"].Rows.Add(temp);
                }
                reader.Dispose();
                com.CommandText = " select * from detail_types";
                reader = com.ExecuteReader();
                while (reader.Read())
                {
                    object[] temp = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                        temp[i] = reader[i];

                    D.Tables["detail_types"].Rows.Add(temp);
                }
                reader.Dispose();
                com.CommandText = " select * from types";
                reader = com.ExecuteReader();
                while (reader.Read())
                {
                    object[] temp = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                        temp[i] = reader[i];

                    D.Tables["types"].Rows.Add(temp);
                }
                reader.Dispose();
                com.CommandText = " select * from users";
                reader = com.ExecuteReader();
                while (reader.Read())
                {
                    object[] temp = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                        temp[i] = reader[i];

                    D.Tables["users"].Rows.Add(temp);
                }
                reader.Dispose();
                com.CommandText = " select * from active_directory";
                reader = com.ExecuteReader();
                while (reader.Read())
                {
                    object[] temp = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                        temp[i] = reader[i];

                    D.Tables["active_directory"].Rows.Add(temp);
                }

            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
                    

        }

        public object[] getRecordById(int id)
        {
            
            OdbcCommand com = new OdbcCommand("select * from items where id="+id);
            com.Connection = con;
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcDataReader reader = com.ExecuteReader();
            object[] array = new object[reader.FieldCount+2];

            
            while (reader.Read())
            {
               
                for (int i = 0; i < reader.FieldCount; i++)
                    array[i] = reader[i];
            }
            
            reader.Dispose();
            /*if (array[9] != DBNull.Value)
            {
                com.CommandText = "select name from users where id = " + array[9].ToString();
                array[9] = com.ExecuteScalar();
            }*/
            if (array[8] != DBNull.Value)
            {
                com.CommandText = "select name from types where id = " + array[8].ToString();
                array[8] = (string)com.ExecuteScalar();
            }

            if (array[9] != DBNull.Value)
            {
                com.CommandText = "select name from active_directory where id = " + array[9].ToString();
                array[9] = (string)com.ExecuteScalar();
            }

            com.CommandText = "select name from users where id =(select user_id from ownership where item_id = " + id + " order by start_date desc limit 1)";

            array[array.Length - 2] = com.ExecuteScalar();
            if (array[array.Length-2] != null)
                array[array.Length - 2] = array[array.Length - 2].ToString();
            else
                array[array.Length - 2] = "";

            com.CommandText = "select details.id,detail_types.name,details.value  from details,detail_types where details.detail_type_id = detail_types.id AND (details.item_id = "+id+")";
            OdbcDataReader reader2 = com.ExecuteReader();
            

            
            DataTable table = new DataTable();
            table.Columns.Add("id");
            table.Columns.Add("name");
            table.Columns.Add("value");
            while (reader2.Read())
            {

                table.Rows.Add(new object[] { reader2[0], reader2[1], reader2[2] });
            }



            array[array.Length - 1] = table;

            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return array;
        }

        public DataTable gettypes()
        {
            DataTable t = new DataTable();
            t.Columns.Add("id");
            t.Columns.Add("name");
            t.Columns.Add("Description");
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand get = new OdbcCommand("select * from types", con);

            using (OdbcDataReader reader = get.ExecuteReader())
            {
                
                
                while (reader.Read())
                    t.Rows.Add(new object[] { reader[0].ToString(), reader[1].ToString(), reader[2].ToString() });
               
            }
            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return t;
        }
        public DataTable getdepressiation()
        {
            DataTable t = new DataTable();
            t.Columns.Add("id");
            t.Columns.Add("name");
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand get = new OdbcCommand("select id, name from depresionation", con);

            using (OdbcDataReader reader = get.ExecuteReader())
            {


                while (reader.Read())
                    t.Rows.Add(new object[] { reader[0].ToString(), reader[1].ToString() });

            }
            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return t;
        }
        public DataTable getcategory()
        {
            DataTable t = new DataTable();
            t.Columns.Add("id");
            t.Columns.Add("name");
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand get = new OdbcCommand("select id, name from category", con);

            using (OdbcDataReader reader = get.ExecuteReader())
            {


                while (reader.Read())
                    t.Rows.Add(new object[] { reader[0].ToString(), reader[1].ToString() });

            }
            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return t;
        }

        public DataTable getdetail_types()
        {
            DataTable t = new DataTable();
            t.Columns.Add("id");
            t.Columns.Add("name");
            t.Columns.Add("Descripition");
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand get = new OdbcCommand("select * from detail_types", con);

            using (OdbcDataReader reader = get.ExecuteReader())
            {


                while (reader.Read())
                    t.Rows.Add(new object[] { reader[0].ToString(), reader[1].ToString(), reader[2].ToString() });

            }
            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return t;
        }

        public DataTable getusers()
        {
            DataTable t = new DataTable();
            t.Columns.Add("id");
            t.Columns.Add("name");
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Failed to connect to server");
                return null;
            }

            OdbcCommand get = new OdbcCommand("select id, name from users", con);

            using (OdbcDataReader reader = get.ExecuteReader())
            {


                while (reader.Read())
                    t.Rows.Add(new object[] { reader[0].ToString(), reader[1].ToString() });

            }
            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return t;
        }

        public DataTable getCondtions()
        {
            DataTable t = new DataTable();
            t.Columns.Add("id");
            t.Columns.Add("name");
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand get = new OdbcCommand("select id, name from conditions", con);

            using (OdbcDataReader reader = get.ExecuteReader())
            {


                while (reader.Read())
                    t.Rows.Add(new object[] { reader[0].ToString(), reader[1].ToString() });

            }
            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return t;
        }

        public DataTable gethistory(int id)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand(" select ownership.start_date,ownership.end_date , users.name from ownership , users where (ownership.item_id = "+id+") AND (users.id = ownership.user_id) order by ownership.start_date desc",con);
            
            DataTable t = new DataTable();

            t.Columns.Add("Start_date");
            t.Columns.Add("End_date");
            t.Columns.Add("Name");
            OdbcDataReader reader = com.ExecuteReader();
            while(reader.Read())
                t.Rows.Add(reader[0].ToString(), reader[1].ToString(), reader[2].ToString());

            ////if (con.State != ConnectionState.Closed)
               //// con.Close();

            return t;
        }

        public object [] login(string user, string password)
        {
            // this method returns id as integer , security level as int and username as string
            try
            {
                
                if (con.State != ConnectionState.Open)
                    con.Open();
            }
            catch(Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Failed to connect to server");
                return null;
            }

            int sec;
            string name;
            OdbcCommand com = new OdbcCommand("SELECT ValidateLogin('"+user+"','"+password+"') ", con);
            int id = Convert.ToInt32(com.ExecuteScalar());

            com.CommandText = "select securityLevel, name from accounts where id = " + id;
            OdbcDataReader reader = com.ExecuteReader();
            if (reader.Read())
            {
                sec = Convert.ToInt32(reader[0].ToString());
                name = reader[1].ToString();
                
            }

            else
                return null;



            ////if (con.State != ConnectionState.Closed)
               //// con.Close();

            //return t;
            return new object[]{id,sec,name};
        }

        public bool logout(int id)
        {
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcCommand com = new OdbcCommand("Call Logout("+id+")",con);
            try
            {
                com.ExecuteNonQuery();
            }
            catch
            {
                return false;
            }
            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return true;
        }

        public DataTable getaccounts()
        {
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcCommand com = new OdbcCommand("select id,name,SecurityLevel,Pass from accounts where deleted = 0", con);

            DataTable table = new DataTable();
            table.Columns.Add("id");
            table.Columns.Add("Name");
            table.Columns.Add("Security");
            table.Columns.Add("Password");
            OdbcDataReader reader = com.ExecuteReader();
            while (reader.Read())
                table.Rows.Add(reader[0].ToString(), reader[1].ToString(), Convert.ToInt32(reader[2].ToString()), reader[3].ToString());

            
            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return table;
        }

        public DataTable getlog()
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand("select accounts.name,log.Action,log.stamp,log.table_name,log.record_id from log , accounts where log.account_id = accounts.id", con);
            OdbcDataReader reader = com.ExecuteReader();
            DataTable res = new DataTable();
            res.Columns.Add("Name");
            res.Columns.Add("Action");
            
            res.Columns.Add("Stamp");
            res.Columns.Add("table_name");
            res.Columns.Add("record_id");
            while (reader.Read())
                res.Rows.Add(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(),reader[3].ToString(),reader[4].ToString());

            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return res;
        }

        public DataTable getBU()
        {
        if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand("select * from bu", con);
            OdbcDataReader reader = com.ExecuteReader();
            DataTable res = new DataTable();
            res.Columns.Add("id");
            res.Columns.Add("name");
            res.Columns.Add("Description");
            
            
            while (reader.Read())
                res.Rows.Add(reader[0].ToString(), reader[1].ToString(), reader[2].ToString());


            ////if (con.State != ConnectionState.Closed)
               //// con.Close();
            return res;
        }

        public DataTable getcountrybybuid(int buid)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand("select id,name from countries where bu_id ="+buid, con);
            OdbcDataReader reader = com.ExecuteReader();
            DataTable res = new DataTable();
            res.Columns.Add("id");
            res.Columns.Add("name");


            while (reader.Read())
                res.Rows.Add(reader[0].ToString(), reader[1].ToString());

            ////if (con.State != ConnectionState.Closed)
            //// con.Close();
            return res;
        }

        public DataTable getlocationbycountry(int countryid)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand("select id,name from location where Country_id =" + countryid, con);
            OdbcDataReader reader = com.ExecuteReader();
            DataTable res = new DataTable();
            res.Columns.Add("id");
            res.Columns.Add("name");


            while (reader.Read())
                res.Rows.Add(reader[0].ToString(), reader[1].ToString());

            ////if (con.State != ConnectionState.Closed)
            //// con.Close();
            return res;
        }

        public DataTable getcountries()
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand("select countries.id,countries.name,bu.name,bu.id from countries,bu where bu.id = countries.bu_id", con);
            OdbcDataReader reader = com.ExecuteReader();
            DataTable res = new DataTable();
            res.Columns.Add("id");
            res.Columns.Add("name");
            res.Columns.Add("buname");
            res.Columns.Add("buid");


            while (reader.Read())
                res.Rows.Add(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString());

            ////if (con.State != ConnectionState.Closed)
            //// con.Close();
            return res;
        }

        public DataTable getlocations()
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand("select locations.id,locations.name,locations.address,locations.description,countries.name,countries.id from countries,locations where countries.id = locations.country_id", con);
            OdbcDataReader reader = com.ExecuteReader();
            DataTable res = new DataTable();
            res.Columns.Add("id");
            res.Columns.Add("name");
            res.Columns.Add("address");
            res.Columns.Add("Description");
            res.Columns.Add("countryname");
            res.Columns.Add("countryid");


            while (reader.Read())
                res.Rows.Add(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), reader[5].ToString());

            ////if (con.State != ConnectionState.Closed)
            //// con.Close();
            return res;
        }

        public DataTable getdepartments()
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand("select department from users", con);
            OdbcDataReader reader = com.ExecuteReader();

            ArrayList ar = new ArrayList();


            while (reader.Read())
                ar.Add(reader[0].ToString());

            DataTable res = new DataTable();
            res.Columns.Add("id");            
           
            return res;
        }

        public ArrayList removeDuplicate(ArrayList data)
        {
            ArrayList res = new ArrayList();
            foreach (object o in data)
                if (!res.Contains(o))
                    res.Add(o);
            return res;
        }

        public DB ItemCard(int ItemId)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            OdbcCommand com = new OdbcCommand("select items.ID,items.name,purchase_date 'PurchaseDate',types.name 'Type' from items,types where items.type_id= types.id and items.id = "+ItemId, con);            

            DB d = new DB();
            OdbcDataAdapter adp = new OdbcDataAdapter(com);
            adp.Fill(d.ItemLabel);

            return d;
        }
        public int GetUseridforitem(int ItemId)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();

                OdbcCommand com = new OdbcCommand("select user_id from ownership where start_date <= '" + formatdatetime(DateTime.Now) + "' and end_date >= '" + formatdatetime(DateTime.Now) + "' and item_id = " + ItemId, con);

                int id = Convert.ToInt32(com.ExecuteScalar().ToString());


                return id;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public string GetUserNameforitem(int ItemId)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();

                OdbcCommand com = new OdbcCommand("select  users.name  from ownership,users where user_id = users.id and end_date >= '" + formatdatetime(DateTime.Now) + "' and start_date <= '" + formatdatetime(DateTime.Now) + "' and item_id = " + ItemId, con);

                return com.ExecuteScalar().ToString();

                
            }
            catch (Exception ex)
            {
                return "" ;
            }
        }

        private string formatdatetime(DateTime d)
        {
            return d.Year + "-" + d.Month + "-" + d.Day;
        }

        public string GetUserNameforuserid(int userid)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();

                OdbcCommand com = new OdbcCommand("select  users.name  from users where id = "+userid, con);

                return com.ExecuteScalar().ToString();


            }
            catch (Exception ex)
            {
                return "";
            }
        }

        public string GetDepartmentforuserid(int userid)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();

                OdbcCommand com = new OdbcCommand("select  users.department  from users where id = " + userid, con);

                return com.ExecuteScalar().ToString();


            }
            catch (Exception ex)
            {
                return "";
            }
        }

        public string GetLoginforuserid(int userid)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();

                OdbcCommand com = new OdbcCommand("select  users.loginid  from users where id = " + userid, con);

                return com.ExecuteScalar().ToString();


            }
            catch (Exception ex)
            {
                return "";
            }
        }

        

       
    }
}
